package com.ydzt.fanka.server.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;

import com.ydzt.fanka.server.domain.CntRepInfo;
import com.ydzt.fanka.server.domain.CommunicationNode;
import com.ydzt.fanka.server.domain.StatisticsInfo;
import com.ydzt.fanka.server.init.InitData;

public class DBHelper {
	static Logger logger = Logger.getLogger(DBHelper.class.getName());

	public static boolean isExsitTransactionCompareByStmtDay(
			String supplier_id, String strStmtDay) {
		boolean isExsit = false;
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			String sqlQuery = "select count(*) count from tbl_centre_transaction_compare where supplier_id = '"
					+ supplier_id + "' and stmt_day = '" + strStmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.createStatement();
			rs = pstmt.executeQuery(sqlQuery);
			while (rs.next()) {
				count = rs.getInt("count");
			}
			conn.commit();
			conn.setAutoCommit(true);
			if (count != 0) {
				isExsit = true;
			} else {
				isExsit = false;
			}
		} catch (Exception e) {

			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isExsit;
	}

	public static HashMap<String, String> getSupplierAndIPMap() {

		HashMap<String, String> map = new HashMap<String, String>();
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		String sqlQuery = "select node_id,node_ip||':'||node_port node_ip from TBL_CENTRE_NODE_IP where upper(node_type) in ('DR','CENTRE')";
		try {
			String tmpID = null;
			String tmpIP = null;
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			// pstmt = conn.prepareStatement(sqlText);
			pstmt = conn.createStatement();
			rs = pstmt.executeQuery(sqlQuery);
			while (rs.next()) {
				tmpID = rs.getString("node_id") + "000000";
				tmpIP = rs.getString("node_ip");
				logger.info("查询到食堂节点:" + tmpID + "/" + tmpIP);
				map.put(tmpID, tmpIP);
			}
		} catch (Exception e) {
			logger.error("查询TBL_CENTRE_NODE_IP信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return map;
	}

	/*
	 * public static String getSupplierByIP(String ip){ Connection conn = null;
	 * Statement pstmt = null; ResultSet rs = null; String nodeID = null; String
	 * sqlText = "select node_id from TBL_CENTRE_NODE_IP where node_ip = '" + ip
	 * + "'"; try { conn = JdbcUtils.getConnectionForOracle();
	 * conn.setAutoCommit(false); //pstmt = conn.prepareStatement(sqlText);
	 * pstmt = conn.createStatement(); rs = pstmt.executeQuery(sqlText);
	 * while(rs.next()){ nodeID = rs.getString("node_id"); } conn.commit();
	 * conn.setAutoCommit(true); } catch (SQLException e) {
	 * logger.error("查询cntrep报表信息出错:" + e.getMessage()); try{ if(conn != null){
	 * conn.rollback(); conn.setAutoCommit(true); } } catch(SQLException e2){ }
	 * } finally{ JdbcUtils.freeAll(rs, pstmt, conn); } return nodeID; }
	 */

	public static int updateAccountInfo() {
		logger.info("已调用卡账户更新");
		int flag = -1;
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;

		String stmt_day = getStmtDay();
		String month = stmt_day.substring(4, 6);
		String sqlText = "";
		sqlText += " update tbl_centre_account a";
		sqlText += "    set (QB_AMOUNT, DEAL_AMOUNT, CARD_COUNTER, DEAL_TIME, TRANS_TYPE) =";
		sqlText += "        (select case";
		sqlText += "                  when (t1.trans_type = 3) then";
		sqlText += "                   t1.begin_amount - t1.amount";
		sqlText += "                  ELSE";
		sqlText += "                   t1.begin_amount + t1.amount";
		sqlText += "                END,";
		sqlText += "                t1.amount,";
		sqlText += "                t1.CARD_COUNTER,";
		sqlText += "                t1.DEAL_TIME,";
		sqlText += "                t1.trans_type";
		sqlText += "           from TBL_CENTRE_TRANSACTION_" + month + " t1,";
		sqlText += "                (select max(DEAL_TIME) DEAL_TIME, upper(trim(cardid))";
		sqlText += "                   from TBL_CENTRE_TRANSACTION_" + month
				+ " cc";
		sqlText += "                  where stmt_day = '" + stmt_day + "'";
		sqlText += "                  group by upper(trim(cardid))) t2";
		sqlText += "          where upper(trim(t1.cardid)) = upper(trim(t2.cardid))";
		sqlText += "            and t1.DEAL_TIME = t2.DEAL_TIME";
		sqlText += "            and upper(a.account_cardid) = upper(trim(t2.cardid)))";
		sqlText += " where upper(a.account_cardid) in (select distinct upper(cardid) from TBL_CENTRE_TRANSACTION_"
				+ month + " where stmt_day = '" + stmt_day + "')";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.createStatement();
			int count = pstmt.executeUpdate(sqlText);

			// pstmt.executeUpdate();
			conn.commit();
			logger.info("执行语句:" + sqlText);
			logger.info("对" + count + "条卡账户进行了更新");
			conn.setAutoCommit(true);
			flag = 0;
			logger.info("卡账户更新成功");
		} catch (Exception e) {
			logger.error("更新卡账户信息出错!" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = -1;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	public static int getAllNonUpdateDetail() {
		int count = -1;
		String stmt_day = getStmtDay();
		String month = stmt_day.substring(4, 6);
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		String sqlText = "select count(*) count from TBL_CENTRE_TRANSACTION_"
				+ month + " where trans_seq is null  and stmt_day = '"
				+ stmt_day + "'";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			// pstmt = conn.prepareStatement(sqlText);
			pstmt = conn.createStatement();
			rs = pstmt.executeQuery(sqlText);
			while (rs.next()) {
				count = rs.getInt("count");
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询cntrep报表信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return count;
	}

	public static boolean createReportDataCNTREP() {
		// 20130712 消费统计的交易类型查询条件修改为trans_type in (3,7)
		String supplier_id = "";
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		List<CntRepInfo> cntRepInfos = new ArrayList<CntRepInfo>();
		// 取结算日期
		String stmtday = DBHelper.getStmtDay();
		String month = stmtday.substring(4, 6);
		boolean flag = false;
		String sqlText = "";
		sqlText += " select t11.STMT_DAY stmt_day,t11.depart depart,t11.supplier_id supplier_id,t11.begin_amount begin_amount,t12.czjy czjy,t12.yzjy yzjy,t12.qcjy qcjy,t12.stxf stxf,t12.sdxf sdxf,t12.lyxf lyxf,t13.end_amount end_amount from ";
		sqlText += " (select t6.STMT_DAY,t6.depart,t6.supplier_id,sum(t6.begin_amount) as begin_amount from";
		sqlText += " (select UPPER(t.CARDID) as cardid,min(t.deal_time) as deal_time";
		sqlText += "  from TBL_CENTRE_TRANSACTION_" + month + " t";
		sqlText += "  where STMT_DAY = '" + stmtday + "'";
		sqlText += "  group by UPPER(CARDID)) t5,";
		sqlText += " (select t3.stmt_day,UPPER(t3.CARDID) as cardid,t3.begin_amount,t3.deal_time as deal_time,";
		sqlText += "                substr(t4.account_workno,0,6) as depart,";
		sqlText += "                t3.supplier_id";
		sqlText += "           from TBL_CENTRE_TRANSACTION_" + month
				+ " t3, TBL_CENTRE_ACCOUNT t4 ";
		sqlText += "          where upper(t3.cardid) = t4.account_cardid ";
		sqlText += "            and STMT_DAY = '" + stmtday + "') t6 ";
		sqlText += "            where t5.cardid = t6.cardid ";
		sqlText += "            and t5.deal_time = t6.deal_time ";
		sqlText += "            group by t6.STMT_DAY,depart,supplier_id )  t11, ";
		sqlText += " (select stmt_day,depart,sum(decode(trans_type,'czjy',amount,0)) as czjy, ";
		sqlText += "          sum(decode(trans_type,'yzjy',amount,0)) as yzjy, ";
		sqlText += "         sum(decode(trans_type,'qcjy',amount,0)) as qcjy, ";
		sqlText += "         sum(decode(trans_type,'stxf',amount,0)) as stxf, ";
		sqlText += "         sum(decode(trans_type,'sdxf',amount,0)) as sdxf, ";
		sqlText += "         sum(decode(trans_type,'lyxf',amount,0)) as lyxf, ";
		sqlText += "         sum(decode(trans_type,'wzjy',amount,0)) as wzjy,supplier_id from ";
		sqlText += " (select stmt_day,trans_type, ";
		sqlText += "        depart, ";
		sqlText += "        sum(amount) as amount, ";
		sqlText += "        supplier_id ";
		sqlText += "   from (select stmt_day,case when t.trans_type = 0 then 'czjy' ";
		sqlText += "             when t.trans_type = 1 then 'yzjy' ";
		sqlText += "             when t.trans_type = 2 then 'qcjy' ";
		sqlText += "             when t.trans_type in (3,7) and substr(equip_id,0,2) = '01' then 'stxf' ";
		sqlText += "             when t.trans_type in (3,7) and substr(equip_id,0,2) = '02' then 'sdxf' ";
		sqlText += "             when t.trans_type in (3,7) and substr(equip_id,0,2) = '03' then 'lyxf' ";
		sqlText += "             else 'wzjy' end as trans_type, ";
		sqlText += "                substr(t1.account_workno,0,6) as depart, ";
		sqlText += "                t.amount, ";
		sqlText += "                t.supplier_id ";
		sqlText += "           from TBL_CENTRE_TRANSACTION_" + month
				+ " t, TBL_CENTRE_ACCOUNT t1 ";
		sqlText += "          where upper(t.cardid) = t1.account_cardid ";
		sqlText += "            and STMT_DAY = '" + stmtday + "' ) ";
		sqlText += "  group by stmt_day,trans_type, ";
		sqlText += "           depart, ";
		sqlText += "           supplier_id) group by stmt_day,depart,supplier_id ) t12, ";
		sqlText += " (select t10.STMT_DAY,t10.depart,t10.supplier_id,sum(t10.begin_amount + t10.amount) as end_amount from ";
		sqlText += " (select UPPER(t.CARDID) as cardid,max(t.deal_time) as deal_time ";
		sqlText += "  from TBL_CENTRE_TRANSACTION_" + month + " t ";
		sqlText += "  where STMT_DAY = '" + stmtday + "' ";
		sqlText += "  group by UPPER(CARDID)) t9, ";
		sqlText += " (select t7.stmt_day,UPPER(t7.CARDID) as cardid,t7.begin_amount,t7.deal_time as deal_time, ";
		sqlText += "				case ";
		sqlText += "    			when t7.trans_type in (3,7) then ";
		sqlText += "     			t7.amount * -1";
		sqlText += "    			else";
		sqlText += "      			t7.amount";
		sqlText += "  				end as amount,";
		sqlText += "                substr(t8.account_workno,0,6) as depart, ";
		sqlText += "                t7.supplier_id ";
		sqlText += "           from TBL_CENTRE_TRANSACTION_" + month
				+ " t7, TBL_CENTRE_ACCOUNT t8 ";
		sqlText += "          where upper(t7.cardid) = t8.account_cardid ";
		sqlText += "            and STMT_DAY = '" + stmtday + "') t10 ";
		sqlText += "            where t9.cardid = t10.cardid ";
		sqlText += "            and t9.deal_time = t10.deal_time ";
		sqlText += "            group by t10.STMT_DAY,depart,supplier_id ) t13 ";
		sqlText += " where t11.depart = t12.depart ";
		sqlText += "      and t11.supplier_id = t12.supplier_id ";
		sqlText += "      and t11.depart = t13.depart ";
		sqlText += "      and t11.supplier_id = t13.supplier_id ";
		sqlText += "      and t11.STMT_DAY =  t12.STMT_DAY ";
		sqlText += "      and t11.STMT_DAY =  t13.STMT_DAY ";

		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			// pstmt = conn.prepareStatement(sqlText);
			pstmt = conn.createStatement();
			rs = pstmt.executeQuery(sqlText);
			while (rs.next()) {
				CntRepInfo cntRepInfo = new CntRepInfo();
				cntRepInfo.setstmt_day(rs.getString("stmt_day"));
				cntRepInfo.setDepart(rs.getString("depart"));
				supplier_id = rs.getString("supplier_id");
				cntRepInfo.setSupplier_ID(rs.getString("supplier_id"));
				cntRepInfo.setBegin_amount(rs.getLong("begin_amount"));
				cntRepInfo.setCzjy(rs.getLong("czjy"));
				cntRepInfo.setYzjy(rs.getLong("yzjy"));
				cntRepInfo.setQcjy(rs.getLong("qcjy"));
				cntRepInfo.setStxf(rs.getLong("stxf"));
				cntRepInfo.setSdxf(rs.getLong("sdxf"));
				cntRepInfo.setLyxf(rs.getLong("lyxf"));
				cntRepInfo.setEnd_amount(rs.getLong("end_amount"));
				cntRepInfos.add(cntRepInfo);
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询cntrep报表信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		// 将cntRepInfos 插入到tbl_centre_cntrep_day
		if (isExistCntrepDay(stmtday, supplier_id)) {
			logger.info("删除" + stmtday + "商户" + supplier_id
					+ "的统计信息(tbl_centre_cntrep_day)");
			deleteCntrepDay(stmtday, supplier_id);
		}

		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.createStatement();
			for (int j = 0; j < cntRepInfos.size(); j++) {
				String sqlInsert = "insert into tbl_centre_cntrep_day(STMT_DAY,DEPART,SUPPLIER_ID,BEGIN_AMOUNT,CZJY,YZJY,QCJY,STXF,SDXF,LYXF,END_AMOUNT)";
				sqlInsert += " values (";
				sqlInsert += "'" + cntRepInfos.get(j).getstmt_day() + "',";
				sqlInsert += "'" + cntRepInfos.get(j).getDepart() + "',";
				sqlInsert += "'" + cntRepInfos.get(j).getSupplier_ID() + "',";
				sqlInsert += "" + cntRepInfos.get(j).getBegin_amount() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getCzjy() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getYzjy() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getQcjy() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getStxf() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getSdxf() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getLyxf() + ",";
				sqlInsert += "" + cntRepInfos.get(j).getEnd_amount() + ")";
				pstmt.addBatch(sqlInsert);

				sqlInsert = "";
			}
			@SuppressWarnings("unused")
			int[] result = pstmt.executeBatch();
			conn.commit();
			logger.info("向tbl_centre_cntrep_day中插入   " + cntRepInfos.size()
					+ "   条记录");
			flag = true;
			conn.setAutoCommit(true);
		} catch (SQLException e1) {
			logger.error("在tbl_centre_cntrep_day中插入数据失败" + e1.getMessage());
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	private static boolean isExistCntrepDay(String stmtDay, String supplier_id) {
		boolean isExist = true;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			String selSql = "select count(*) count from tbl_centre_cntrep_day where supplier_id = '"
					+ supplier_id + "' and stmt_day = '" + stmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(selSql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
				if (count == 0) {
					isExist = false;
				} else {
					logger.info("tbl_centre_cntrep_day有统计信息:" + count + "条!");
					isExist = true;
				}
			}
		} catch (Exception e) {
			logger.error("查询统计信息是否存在出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				logger.error("查询统计信息是否存在出错:" + e2.getMessage());
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isExist;
	}

	public static String getEndState() {
		String sqlText = "select STMT_STATUS from TBL_CENTRE_STMT_CTL where trim(STMT_TYPE) = 'END_STATE' and STMT_DAY = (select stmt_day from tbl_centre_clearday)";
		String endState = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				endState = rs.getString("STMT_STATUS").trim();
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询日终状态出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return endState;
	}

	public static void setEndState(String state) {
		// String sqlText =
		// "select CODE_VALUE from TBL_CENTRE_CODE_LIST where trim(CODE_INTEGER) = 'STATISTICS_STATE'";
		String sqlText = "update TBL_CENTRE_CODE_LIST set CODE_VALUE = '"
				+ state + "' where trim(CODE_INTEGER) = 'END_STATE'";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("设置日终状态出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
	}

	public static void setStatisticsState(String state) {
		String sqlText = "update TBL_CENTRE_CODE_LIST set CODE_VALUE = '"
				+ state + "' where trim(CODE_INTEGER) = 'STATISTICS_STATE'";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("设置明细入同级状态出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
	}

	public static String getStatisticsState() {
		String sqlText = "select CODE_VALUE from TBL_CENTRE_CODE_LIST where trim(CODE_INTEGER) = 'STATISTICS_STATE'";
		String State = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				State = rs.getString("CODE_VALUE").trim();
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询明细入统计状态出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return State;
	}

	public static String getUpdateAccountState() {
		String sqlText = "select CODE_VALUE from TBL_CENTRE_CODE_LIST where trim(CODE_INTEGER) = 'UPDATE_ACCOUNT_STATE'";
		String State = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				State = rs.getString("CODE_VALUE").trim();
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询卡账户更新状态出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return State;
	}

	// 首先删除可能存在的统计信息 再进行插入(食堂)
	public static boolean createStatistics() {
		String supplier_id = "";
		logger.info("调用createStatistics()方法，生成当日统计");
		List<StatisticsInfo> statisticsInfos = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean flag = false;
		statisticsInfos = new ArrayList<StatisticsInfo>();
		String stmtDay = DBHelper.getStmtDay();
		String month = stmtDay.substring(4, 6);
		String sqlDetailInfo = "";
		sqlDetailInfo += " select supplier_id ,stmt_day,card_type,trans_type,count(*) times,sum(amount) amount ,nvl(substr(equip_id,0,2),'FF') dept_id";
		sqlDetailInfo += " from ";
		sqlDetailInfo += " tbl_centre_transaction_" + month
				+ " where stmt_day = '" + stmtDay + "'";
		sqlDetailInfo += " group by supplier_id,stmt_day,card_type,trans_type,nvl(substr(equip_id,0,2),'FF')";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlDetailInfo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				StatisticsInfo statisticsInfo = new StatisticsInfo();
				supplier_id = rs.getString("supplier_id");
				statisticsInfo.setSupplier_id(rs.getString("supplier_id"));
				statisticsInfo.setStmt_day(rs.getString("stmt_day"));
				statisticsInfo.setCard_type(rs.getInt("card_type"));
				statisticsInfo.setTrans_type(rs.getInt("trans_type"));
				statisticsInfo.setTimes(rs.getLong("times"));
				statisticsInfo.setAmount(rs.getLong("amount"));
				statisticsInfo.setDept_id(rs.getString("dept_id"));
				statisticsInfos.add(statisticsInfo);
			}

			// 先删除可能存在的统计信息 然后再插入
			if (isExist(supplier_id, stmtDay)) {
				logger.info("删除" + stmtDay + "的统计信息");
				deleteTableTransactionDayByStmtDay(stmtDay);
			}

			StringBuilder sBuilder = new StringBuilder();
			for (int i = 0; i < statisticsInfos.size(); i++) {
				sBuilder.append("insert into tbl_centre_transaction_day");
				//2014 03 21 增加部门编号dept_id
				sBuilder.append("(supplier_id,stmt_day,card_type,trans_type,times,amount,err_code,dept_id) values(");
				sBuilder.append("'" + statisticsInfos.get(i).getSupplier_id()
						+ "',");
				sBuilder.append("'" + statisticsInfos.get(i).getStmt_day()
						+ "',");
				sBuilder.append(statisticsInfos.get(i).getCard_type() + ",");
				sBuilder.append(statisticsInfos.get(i).getTrans_type() + ",");
				sBuilder.append(statisticsInfos.get(i).getTimes() + ",");
				sBuilder.append(statisticsInfos.get(i).getAmount() + ",'00',");
				sBuilder.append("'" + statisticsInfos.get(i).getDept_id() + "'");
				sBuilder.append(")");
				String sqlText = sBuilder.toString();
				sBuilder.delete(0, sBuilder.length());
				try {
					pstmt = conn.prepareStatement(sqlText);
					pstmt.executeQuery();
					conn.commit();
				} catch (SQLException e) {
					logger.error("插入tbl_centre_transaction_day出错!"
							+ e.getMessage());
				}
			}
			conn.setAutoCommit(true);
			flag = true;
			logger.info("进行日终步骤---->对明细进行统计完成!");
		} catch (SQLException e) {
			logger.error("明细入统计出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	private static boolean deleteCntrepDay(String strStmtDay, String supplier_id) {
		boolean isSuccess = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String delSql = "delete from tbl_centre_cntrep_day where supplier_id = '"
					+ supplier_id + "' and stmt_day = '" + strStmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(delSql);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			isSuccess = true;
		} catch (Exception e) {
			logger.error("中心删除tbl_centre_cntrep_day统计信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				logger.error("中心删除tbl_centre_cntrep_day统计信息出错:"
						+ e2.getMessage());
			}
			isSuccess = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isSuccess;
	}

	// 食堂调用此方法删除统计信息
	private static boolean deleteTableTransactionDayByStmtDay(String strStmtDay) {
		boolean isSuccess = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String delSql = "delete from tbl_centre_transaction_day where stmt_day = '"
					+ strStmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(delSql);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			isSuccess = true;
		} catch (Exception e) {
			logger.error("食堂删除统计信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				logger.error("中心删除统计信息出错:" + e2.getMessage());
			}
			isSuccess = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isSuccess;
	}

	private static boolean isExist(String supplier_id, String strStmtDay) {
		boolean isExist = true;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			String selSql = "select count(*) count from tbl_centre_transaction_day where supplier_id = '"
					+ supplier_id + "' and stmt_day = '" + strStmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(selSql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
				if (count == 0) {
					isExist = false;
				} else {
					logger.info("tbl_centre_transaction_day用统计信息:" + count
							+ "条!");
					isExist = true;
				}
			}
		} catch (Exception e) {
			logger.error("查询统计信息是否存在出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				logger.error("查询统计信息是否存在出错:" + e2.getMessage());
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isExist;
	}

	// 中心调用此方法删除统计信息
	private static boolean deleteTableTransactionDayByStmtDay(
			String supplier_id, String strStmtDay) {
		boolean isSuccess = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String delSql = "delete from tbl_centre_transaction_day where supplier_id = '"
					+ supplier_id + "' and stmt_day = '" + strStmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(delSql);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			isSuccess = true;
		} catch (Exception e) {
			logger.error("中心删除统计信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				logger.error("中心删除统计信息出错:" + e2.getMessage());
			}
			isSuccess = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isSuccess;
	}

	// 中心调用此方法删除食堂上传的对账信息
	public static boolean deleteTableTransactionCompareByStmtDay(
			String supplier_id, String strStmtDay) {
		boolean isSuccess = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String delSql = "delete from tbl_centre_transaction_compare where supplier_id = '"
					+ supplier_id + "' and stmt_day = '" + strStmtDay + "'";
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(delSql);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			isSuccess = true;
		} catch (Exception e) {
			logger.error("中心删除" + supplier_id + "在" + strStmtDay + "的对账信息出错:"
					+ e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				logger.error("中心删除" + supplier_id + "在" + strStmtDay
						+ "的对账信息出错:" + e2.getMessage());
			}
			isSuccess = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return isSuccess;
	}

	/*
	 * 中心调用此方法生成统计信息 首先删除可能存在的统计信息 再进行插入(中心)
	 */
	public static boolean createStatistics(String supplier_id, String stmtday) {
		logger.info("调用createStatistics()方法，生成商户" + supplier_id + "的当日统计");
		List<StatisticsInfo> statisticsInfos = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean flag = false;
		statisticsInfos = new ArrayList<StatisticsInfo>();
		String month = stmtday.substring(4, 6);
		String sqlDetailInfo = "";
		sqlDetailInfo += " select supplier_id ,stmt_day,card_type,trans_type,count(*) times,sum(amount) amount ,nvl(substr(equip_id,0,2),'FF') dept_id";
		sqlDetailInfo += " from ";
		sqlDetailInfo += " tbl_centre_transaction_" + month
				+ " where stmt_day = '" + stmtday + "' and supplier_id ='"
				+ supplier_id + "'";
		sqlDetailInfo += " group by supplier_id,stmt_day,card_type,trans_type,nvl(substr(equip_id,0,2),'FF')";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlDetailInfo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				StatisticsInfo statisticsInfo = new StatisticsInfo();
				statisticsInfo.setSupplier_id(rs.getString("supplier_id"));
				statisticsInfo.setStmt_day(rs.getString("stmt_day"));
				statisticsInfo.setCard_type(rs.getInt("card_type"));
				statisticsInfo.setTrans_type(rs.getInt("trans_type"));
				statisticsInfo.setTimes(rs.getLong("times"));
				statisticsInfo.setAmount(rs.getLong("amount"));
				statisticsInfo.setDept_id(rs.getString("dept_id"));
				statisticsInfos.add(statisticsInfo);
			}

			if (isExist(supplier_id, stmtday)) {
				deleteTableTransactionDayByStmtDay(supplier_id, stmtday);
				logger.info("删除" + stmtday + "商户" + supplier_id + "的统计信息");
			}

			StringBuilder sBuilder = new StringBuilder();
			for (int i = 0; i < statisticsInfos.size(); i++) {
				sBuilder.append("insert into tbl_centre_transaction_day");
				sBuilder.append("(supplier_id,stmt_day,card_type,trans_type,times,amount,err_code,dept_id) values(");
				sBuilder.append("'" + statisticsInfos.get(i).getSupplier_id()
						+ "',");
				sBuilder.append("'" + statisticsInfos.get(i).getStmt_day()
						+ "',");
				sBuilder.append(statisticsInfos.get(i).getCard_type() + ",");
				sBuilder.append(statisticsInfos.get(i).getTrans_type() + ",");
				sBuilder.append(statisticsInfos.get(i).getTimes() + ",");
				sBuilder.append(statisticsInfos.get(i).getAmount() + ",'00',");
				sBuilder.append("'" + statisticsInfos.get(i).getDept_id() + "'");
				sBuilder.append(")");
				String sqlText = sBuilder.toString();
				sBuilder.delete(0, sBuilder.length());
				try {
					pstmt = conn.prepareStatement(sqlText);
					pstmt.executeQuery();
					conn.commit();
				} catch (SQLException e) {
					logger.error("插入tbl_centre_transaction_day出错!"
							+ e.getMessage());
				}
			}
			conn.setAutoCommit(true);
			flag = true;
			logger.info("进行日终步骤---->对明细进行统计完成!");
		} catch (SQLException e) {
			logger.error("明细入统计出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	public static void setStmtCtlStatusByType(String stmt_type,
			String stmt_state) {
		String sqlText = "update TBL_CENTRE_STMT_CTL set STMT_STATUS = '"
				+ stmt_state + "' where trim(STMT_TYPE) = '" + stmt_type
				+ "' and STMT_DAY = (SELECT STMT_DAY FROM TBL_CENTRE_CLEARDAY)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("设置结算控制表出错:" + e.getMessage());
			logger.error("出错的SQL为:" + sqlText);
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
	}

	public static void setUpdateAccountState(String state) {
		String sqlText = "update TBL_CENTRE_CODE_LIST set CODE_VALUE = '"
				+ state + "' where trim(CODE_INTEGER) = 'STATISTICS_STATE'";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("设置明细入同级状态出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
	}

	public static String getStmtDay() {
		String sqlText = "select stmt_day from tbl_centre_clearday";
		String stmtDay = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();

			if (conn != null) {
				conn.setAutoCommit(false);
				pstmt = conn.prepareStatement(sqlText);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					stmtDay = rs.getString("stmt_day");
				}
				conn.commit();
				conn.setAutoCommit(true);
			}
		} catch (SQLException e) {
			logger.error("查询结算日期出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return stmtDay;
	}

	/*
	 * 设置结算日期(将当前结算日期加一)
	 */
	public static boolean setAutoStmtDay() {
		String currentStmtDay = "";
		boolean flag = false;
		try {
			currentStmtDay = getStmtDay();
			logger.info("当前结算日期为:" + currentStmtDay + "将自动增长一天...");
			String afterStmtDay = Convert.getSpecifiedDayAfter(currentStmtDay);
			logger.info("将增长为:" + afterStmtDay);
			flag = setStmtDay(afterStmtDay);
		} catch (Exception e) {
			logger.error("自动切换结算日期发生错误!" + e.getMessage());
		}

		return flag;
	}

	// 设置结算日期，将stmtDay设置为结算日期
	public synchronized static boolean setStmtDay(String stmtDay) {
		boolean flag = false;
		String sqlInsert = "update TBL_CENTRE_CLEARDAY set STMT_DAY = '"
				+ stmtDay + "'";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.executeUpdate(sqlInsert);
			conn.commit();
			logger.info("日终完成");
			flag = true;
			conn.setAutoCommit(true);
		} catch (SQLException e1) {
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
					flag = false;
					logger.error("更改stmtday失败 日终不成功");
					logger.error(e1.getMessage());
				}
			} catch (SQLException e2) {
				logger.error(e2.getMessage(), e2);
				flag = false;
			}
		} finally {
			JdbcUtils.freeAll(rs, stmt, conn);
		}
		return flag;
	}

	public static List<CommunicationNode> getOneCommunicationNodeByType(
			String nodeType) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<CommunicationNode> communicationNodes = new ArrayList<CommunicationNode>();
		String sqlText = "select node_id,node_name,node_ip,node_port from tbl_centre_node_ip where node_id = ? ";

		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.setString(1, nodeType);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				CommunicationNode communicateNode = new CommunicationNode();
				communicateNode.setNodeID(rs.getString("node_id"));
				communicateNode.setNodeName(rs.getString("node_name"));
				communicateNode.setNodeType(nodeType);
				communicateNode.setNodeIP(rs.getString("node_ip"));
				communicateNode.setNodePort(rs.getInt("node_port"));
				communicationNodes.add(communicateNode);
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询通信节点信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return communicationNodes;
	}

	/*
	 * 查询通信节点信息
	 * 
	 * @param nodetype 为DR时查询出的节点为食堂 为POS时查询出的节点为POS
	 */
	public static List<CommunicationNode> getCommunicationNodeByType(
			String nodeType) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<CommunicationNode> communicationNodes = new ArrayList<CommunicationNode>();
		String sqlText = "select node_id,node_name,node_ip,node_port from tbl_centre_node_ip where node_type = ? and pr_id = ?";

		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.setString(1, nodeType);
			pstmt.setString(2, InitData.nodeType);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				CommunicationNode communicateNode = new CommunicationNode();
				communicateNode.setNodeID(rs.getString("node_id"));
				communicateNode.setNodeName(rs.getString("node_name"));
				communicateNode.setNodeType(nodeType);
				communicateNode.setNodeIP(rs.getString("node_ip"));
				communicateNode.setNodePort(rs.getInt("node_port"));
				communicationNodes.add(communicateNode);
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询通信节点信息出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return communicationNodes;
	}

	public static boolean createStmtCtlRecordByStmtDay(String stmtday) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		boolean flag = false;
		String sqlText_createSPLIT_FLAG = "insert into TBL_CENTRE_STMT_CTL(STMT_DAY,STMT_SEQ,STMT_TYPE,STMT_DESC,STMT_STATUS,REMARK)";
		sqlText_createSPLIT_FLAG += " values('" + stmtday
				+ "',1,'SPLIT_FLAG','清分处理状态','WAIT','  ')";

		String sqlText_createSTATISTICS_STATE = "insert into TBL_CENTRE_STMT_CTL(STMT_DAY,STMT_SEQ,STMT_TYPE,STMT_DESC,STMT_STATUS,REMARK)";
		sqlText_createSTATISTICS_STATE += " values('" + stmtday
				+ "',4,'STATISTICS_STATE','统计信息生成','WAIT','  ')";

		String sqlText_createUPDATE_ACCOUNT_STATE = "insert into TBL_CENTRE_STMT_CTL(STMT_DAY,STMT_SEQ,STMT_TYPE,STMT_DESC,STMT_STATUS,REMARK)";
		sqlText_createUPDATE_ACCOUNT_STATE += " values('" + stmtday
				+ "',2,'UPDATE_ACCOUNT_STATE','卡账户信息更新','WAIT','  ')";

		String sqlText_createEND_STATE = "insert into TBL_CENTRE_STMT_CTL(STMT_DAY,STMT_SEQ,STMT_TYPE,STMT_DESC,STMT_STATUS,REMARK)";
		sqlText_createEND_STATE += " values('" + stmtday
				+ "',3,'END_STATE','日终状态','WAIT','  ')";

		conn = JdbcUtils.getConnectionForOracle();
		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.addBatch(sqlText_createSPLIT_FLAG);
			stmt.addBatch(sqlText_createSTATISTICS_STATE);
			stmt.addBatch(sqlText_createUPDATE_ACCOUNT_STATE);
			stmt.addBatch(sqlText_createEND_STATE);
			@SuppressWarnings("unused")
			int[] result = stmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
			flag = true;
		} catch (SQLException e) {
			logger.error("生成下一结算日的结算控制表记录");
			logger.error(e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, stmt, conn);
		}
		return flag;
	}

	public static List<String> getCompareInfoByteStmtDay() {
		List<String> suppliers = new ArrayList<String>();
		String sqlText = "select distinct t2.supplier_id supplier_id from TBL_CENTRE_TRANSACTION_COMPARE t1,TBL_CENTRE_SUPPLIER t2 where t1.supplier_id = t2.supplier_id and t1.stmt_day = (select stmt_day from tbl_centre_clearday)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String supplier = rs.getString("supplier_id");
				suppliers.add(supplier);
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("查询结算日期出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return suppliers;
	}

	/*
	 * public static String getSupplierIDByIP(String IP){ String sqlText =
	 * "select NODE_ID from TBL_CENTRE_NODE_IP where NODE_IP ='" + IP + "'";
	 * String supplierID = ""; Connection conn = null; PreparedStatement pstmt =
	 * null; ResultSet rs = null; try { conn =
	 * JdbcUtils.getConnectionForOracle(); conn.setAutoCommit(false); pstmt =
	 * conn.prepareStatement(sqlText); rs = pstmt.executeQuery();
	 * while(rs.next()){ supplierID = rs.getString("NODE_ID"); } conn.commit();
	 * conn.setAutoCommit(true); } catch (SQLException e) { try{ if(conn !=
	 * null){ conn.rollback(); conn.setAutoCommit(true); } } catch(SQLException
	 * e2){ } } finally{ JdbcUtils.freeAll(rs, pstmt, conn); } return
	 * supplierID; }
	 */
}
